package com.luo.backend.repository;

import com.luo.backend.entity.Food;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * 商品数据访问接口
 */
@Repository
public interface FoodRepository extends JpaRepository<Food, Long>, JpaSpecificationExecutor<Food> {
    
    /**
     * 根据商家ID查找商品
     */
    @Query("SELECT f FROM Food f WHERE f.merchantId = :merchantId AND f.status = 1 " +
           "ORDER BY f.isHot DESC, f.isRecommended DESC, f.soldCount DESC")
    List<Food> findByMerchantId(@Param("merchantId") Long merchantId);
    
    /**
     * 根据商家ID和分类查找商品
     */
    @Query("SELECT f FROM Food f WHERE f.merchantId = :merchantId AND f.categoryId = :categoryId AND f.status = 1 " +
           "ORDER BY f.isHot DESC, f.isRecommended DESC, f.soldCount DESC")
    List<Food> findByMerchantIdAndCategoryId(@Param("merchantId") Long merchantId, @Param("categoryId") Long categoryId);
    
    /**
     * 根据商家ID和关键词搜索商品
     */
    @Query("SELECT f FROM Food f WHERE f.merchantId = :merchantId AND f.status = 1 AND f.name LIKE %:keyword% " +
           "ORDER BY f.isHot DESC, f.isRecommended DESC, f.soldCount DESC")
    List<Food> findByMerchantIdAndNameContaining(@Param("merchantId") Long merchantId, @Param("keyword") String keyword);
    
    /**
     * 根据爆款标识查找商品
     */
    @Query("SELECT f FROM Food f WHERE f.merchantId = :merchantId AND f.isHot = :isHot AND f.status = 1 " +
           "ORDER BY f.soldCount DESC")
    List<Food> findByMerchantIdAndIsHot(@Param("merchantId") Long merchantId, @Param("isHot") Integer isHot);
    
    /**
     * 根据推荐标识查找商品
     */
    @Query("SELECT f FROM Food f WHERE f.merchantId = :merchantId AND f.isRecommended = :isRecommended AND f.status = 1 " +
           "ORDER BY f.soldCount DESC")
    List<Food> findByMerchantIdAndIsRecommended(@Param("merchantId") Long merchantId, @Param("isRecommended") Integer isRecommended);
    
    /**
     * 全局搜索商品（分页）
     */
    @Query("SELECT f FROM Food f WHERE f.status = 1 AND f.name LIKE %:keyword% " +
           "ORDER BY f.isHot DESC, f.isRecommended DESC, f.soldCount DESC")
    Page<Food> searchByKeyword(@Param("keyword") String keyword, Pageable pageable);
    
    /**
     * 根据商家ID和状态查找商品（分页）
     */
    @Query("SELECT f FROM Food f WHERE f.merchantId = :merchantId AND f.status = :status " +
           "ORDER BY f.isHot DESC, f.isRecommended DESC, f.soldCount DESC")
    Page<Food> findByMerchantIdAndStatus(@Param("merchantId") Long merchantId, @Param("status") Integer status, Pageable pageable);
    
    /**
     * 根据状态查找商品（分页）
     */
    @Query("SELECT f FROM Food f WHERE f.status = :status " +
           "ORDER BY f.isHot DESC, f.isRecommended DESC, f.soldCount DESC")
    Page<Food> findByStatus(@Param("status") Integer status, Pageable pageable);
    
    /**
     * 根据热门标识和状态查找商品（分页）
     */
    @Query("SELECT f FROM Food f WHERE f.isHot = :isHot AND f.status = :status " +
           "ORDER BY f.soldCount DESC, f.rating DESC")
    Page<Food> findByIsHotAndStatus(@Param("isHot") Integer isHot, @Param("status") Integer status, Pageable pageable);
    
    /**
     * 根据推荐标识和状态查找商品（分页）
     */
    @Query("SELECT f FROM Food f WHERE f.isRecommended = :isRecommended AND f.status = :status " +
           "ORDER BY f.rating DESC, f.soldCount DESC")
    Page<Food> findByIsRecommendedAndStatus(@Param("isRecommended") Integer isRecommended, @Param("status") Integer status, Pageable pageable);
    
    // ========== 商家端查询方法 ==========
    
    /**
     * 根据商家ID查找所有菜品（包括上架和下架的）
     */
    @Query("SELECT f FROM Food f WHERE f.merchantId = :merchantId ORDER BY f.createdAt DESC")
    List<Food> findAllByMerchantIdOrderByCreatedAtDesc(@Param("merchantId") Long merchantId);
    
    /**
     * 根据商家ID和状态查找菜品（不分页）
     */
    @Query("SELECT f FROM Food f WHERE f.merchantId = :merchantId AND f.status = :status ORDER BY f.createdAt DESC")
    List<Food> findByMerchantIdAndStatus(@Param("merchantId") Long merchantId, @Param("status") Integer status);
    
    /**
     * 统计商家菜品总数
     */
    Long countByMerchantId(Long merchantId);
    
    /**
     * 统计商家指定状态的菜品数量
     */
    Long countByMerchantIdAndStatus(Long merchantId, Integer status);
    
    /**
     * 根据商家ID查找商品（分页，按销量排序）- 用于热门商品
     */
    @Query("SELECT f FROM Food f WHERE f.merchantId = :merchantId ORDER BY f.soldCount DESC")
    List<Food> findTopByMerchantIdOrderBySoldCount(@Param("merchantId") Long merchantId, Pageable pageable);
    
    // ========== 搜索功能查询方法 ==========
    
    /**
     * 搜索商品（按商品名或商家名）- 分页
     */
    @Query("SELECT f FROM Food f LEFT JOIN Merchant m ON f.merchantId = m.id " +
           "WHERE f.status = 1 AND (f.name LIKE %:keyword% OR m.name LIKE %:keyword%) " +
           "ORDER BY f.soldCount DESC, f.rating DESC")
    Page<Food> searchFoodsWithMerchant(@Param("keyword") String keyword, Pageable pageable);
    
    /**
     * 统计搜索商品数量（按商品名或商家名）
     */
    @Query("SELECT COUNT(f) FROM Food f LEFT JOIN Merchant m ON f.merchantId = m.id " +
           "WHERE f.status = 1 AND (f.name LIKE %:keyword% OR m.name LIKE %:keyword%)")
    Long countFoodsWithMerchant(@Param("keyword") String keyword);
    
    /**
     * 批量获取商家的预览商品（每个商家最多取指定数量）
     */
    @Query("SELECT f FROM Food f WHERE f.merchantId IN :merchantIds AND f.status = 1 " +
           "ORDER BY f.soldCount DESC")
    List<Food> getPreviewFoodsByMerchantIds(@Param("merchantIds") List<Long> merchantIds);
    
    /**
     * 更新商品评分统计
     */
    @Modifying
    @Query("UPDATE Food f SET f.rating = :rating, f.ratingCount = :ratingCount WHERE f.id = :foodId")
    void updateRatingStatistics(@Param("foodId") Long foodId, 
                                @Param("rating") BigDecimal rating, 
                                @Param("ratingCount") Integer ratingCount);
    
    /**
     * 根据状态查找商品（按销量排序）- AI推荐使用
     */
    @Query("SELECT f FROM Food f WHERE f.status = :status " +
           "ORDER BY f.soldCount DESC, f.rating DESC")
    List<Food> findTop5ByStatusOrderBySoldCountDesc(@Param("status") Integer status, Pageable pageable);
}
